package com.qf.dao;

import com.qf.entity.Student;
import com.qf.utils.DbUtil2;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*
杨威
2021/9/6
15:34
*/
public class StudentDaoImpl {
    public int insert(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtil2.getConnection();
            preparedStatement = connection.prepareStatement("insert into student (stu_name,stu_gender,stu_phone,bornDate,stu_email,stu_address,grade_id)values(?,?,?,?,?,?,?);");
            preparedStatement.setString(1, student.getStuName());
            preparedStatement.setString(2, student.getStuGender());
            preparedStatement.setString(3, student.getStuPhone());
            preparedStatement.setObject(4, student.getBornDate());
            preparedStatement.setString(5, student.getStuEmail());
            preparedStatement.setString(6, student.getStuAddress());
            preparedStatement.setInt(7, student.getGradeId());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil2.closeAll(connection, preparedStatement, null);
        }

        return 0;
    }

    public int update(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtil2.getConnection();
            preparedStatement = connection.prepareStatement("update student set stu_name=?,stu_gender=?,stu_phone=?,bornDate=?,stu_email=?,stu_address=?,grade_id=? where stu_id=?");
            preparedStatement.setString(1, student.getStuName());
            preparedStatement.setString(2, student.getStuGender());
            preparedStatement.setString(3, student.getStuPhone());
            preparedStatement.setObject(4, student.getBornDate());
            preparedStatement.setString(5, student.getStuEmail());
            preparedStatement.setString(6, student.getStuAddress());
            preparedStatement.setInt(7, student.getGradeId());
            preparedStatement.setInt(8, student.getStuId());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil2.closeAll(connection, preparedStatement, null);
        }

        return 0;
    }

    public int delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtil2.getConnection();
            preparedStatement = connection.prepareStatement("delete from student where stu_id=?");
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil2.closeAll(connection, preparedStatement, null);
        }

        return 0;
    }

    public Student selectOne(int stu_id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtil2.getConnection();
            preparedStatement = connection.prepareStatement("select * from student where stu_id=?");
            preparedStatement.setInt(1, stu_id);
            resultSet = preparedStatement.executeQuery();
            Student student=null;
            while (resultSet.next()) {
                int stuId = resultSet.getInt(1);
                String stuName = resultSet.getString(2);
                String stuGender = resultSet.getString(3);
                String stuPhone = resultSet.getString(4);
                Date bornDate = resultSet.getDate(5);
                String stuEmail = resultSet.getString(6);
                String stuAddress = resultSet.getString(7);
                int gradeId = resultSet.getInt(8);
                student=new Student();
                student.setStuId(stuId);
                student.setStuName(stuName);
                student.setStuGender(stuGender);
                student.setStuPhone(stuPhone);
                student.setBornDate(bornDate);
                student.setStuEmail(stuEmail);
                student.setStuAddress(stuAddress);
                student.setGradeId(gradeId);
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil2.closeAll(connection, preparedStatement, resultSet);
        }

        return null;
    }

    public List<Student> selectAll() {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection = DbUtil2.getConnection();
            preparedStatement = connection.prepareStatement("select * from student");
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList=new ArrayList<>();
            Student student=null;
            while (resultSet.next()){
                int stuId = resultSet.getInt(1);
                String stuName = resultSet.getString(2);
                String stuGender = resultSet.getString(3);
                String stuPhone = resultSet.getString(4);
                Date bornDate = resultSet.getDate(5);
                String stuEmail = resultSet.getString(6);
                String stuAddress = resultSet.getString(7);
                int gradeId = resultSet.getInt(8);
                student=new Student();
                student.setStuId(stuId);
                student.setStuName(stuName);
                student.setStuGender(stuGender);
                student.setStuPhone(stuPhone);
                student.setBornDate(bornDate);
                student.setStuEmail(stuEmail);
                student.setStuAddress(stuAddress);
                student.setGradeId(gradeId);
                studentList.add(student);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil2.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }
}
